import pymysql
def updateCode():
    # 打开数据库连接
    db = pymysql.connect("localhost", "root", "everlo1992", "yutube", charset='utf8')

    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    # SQL 查询语句
    sql = "SELECT n1 FROM ysn union all SELECT n2 FROM ysn"
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        allNode = set(results)
        smallList = set()
        maxList = set()
        smallAb = set()
        codeDic = {}
        for itemm in allNode:
            item = itemm[0]
            if item >= 1134890:
                maxList.add(item)
            else:
                # codeDic[item] = item
                smallList.add(item)
        for i in range(1134890):
            if i not in smallList:
                smallAb.add(i)
        while len(maxList)>0 and len(smallAb):
            codeDic[maxList.pop()] = smallAb.pop()
        print('剩余节点情况：',len(smallAb), len(maxList))
        print(codeDic)
        sql1 = 'select n1,n2 from ysn'
        cursor.execute(sql1)
        results =  cursor.fetchall()
        count = 0
        for item in results:
            n1 = item[0]
            n2 = item[1]
            if count == 50:
                count = 0
                db.commit()
                print("---data commited---")
            if n1 < 1134890 and n2 < 1134890:
                continue
            print('---------------------------------')
            if n1 >= 1134890:
                count += 1
                ts = "update ysn set n1=%d where n1 = %d" % (codeDic[n1],n1)
                print(n1,n2,ts)
                cursor.execute(ts)
            if n2 >= 1134890:
                count += 1
                ts = "update ysn set n2=%d where n2 = %d" % (codeDic[n2], n2)
                print(n1, n2, ts)
                cursor.execute(ts)
        db.commit()
    except Exception as e:
        print("Error: unable to fecth data",e)

    # 关闭数据库连接
    db.close()

if __name__ == "__main__":
    updateCode()